package blog.dao.impl;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import blog.dao.UserDao;
import blog.entities.User;
import blog.utils.JdbcUtils;

public class UserDaoImpl implements UserDao {

	private QueryRunner runner = new QueryRunner();
	
	@Override
	public User getUser(String username, String password) {
		String sql = "select * from users where username = ? and password = ?";
		Connection conn = JdbcUtils.getConnection();
		User user = null;
		try {
			user = runner.query(conn,sql,new BeanHandler<>(User.class),username,password);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JdbcUtils.release(conn);
		}
		return user;
	}

	@Override
	public List<User> getUserList() {
		String sql = "select * from users where delete_status = 0";
		Connection conn = JdbcUtils.getConnection();
		List<User> list = null;
		try {
			list = runner.query(conn,sql,new BeanListHandler<>(User.class));
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JdbcUtils.release(conn);
		}
		return list;
	}

	@Override
	public int registerUser(String username,String password) {
		String sql = "INSERT INTO users(username,password) values(?,?)";
		Connection conn = JdbcUtils.getConnection();
		int update = 0;
		try {
			update = runner.update(conn,sql,username,password);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JdbcUtils.release(conn);
		}
		return update;
	}

	@Override
	public List<User> getUserByCondition(Object... params) {
		String sql = "";
		List<User> userList = null;
		Object[] values = null;
		Connection conn = JdbcUtils.getConnection();
		if (params != null) {
			// 添加key到sql
			// 后面的runner.update()中会把sql参数给preparedStatement
			sql = "select * from users where ";
			for (int i = 0; i < params.length; i += 2) {
				if (i == params.length - 2) {
					sql += (String)params[i] + " = ? ";
					break;
				}
				sql += (String)params[i] + " = ? and ";	
			}
			
			// 为添加values到runner.update()准备一个Object[]数组
			values = new Object[params.length / 2];
			for (int i = 1, j = 0; i < params.length; i += 2, j++) {
				values[j] = params[i];
			}
			
			try {
				userList = runner.query(conn, sql, new BeanListHandler<>(User.class),values);
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				JdbcUtils.release(conn);
			}
		}
		
		return userList;
	}

	@Override
	public int deleteUserByCondition(Object... params) {
		String sql = "";
		int update = 0;
		Object[] values = null;
		Connection conn = JdbcUtils.getConnection();
		if (params != null) {
			// 添加key到sql
			// 后面的runner.query()中会把sql参数给preparedStatement
			sql = "update users set delete_status = 1 where ";
			for (int i = 0; i < params.length; i += 2) {
				if (i == params.length - 2) {
					sql += (String)params[i] + " = ? ";
					break;
				}
				sql += (String)params[i] + " = ? and ";	
			}
			
			// 为添加values到runner.query()准备一个Object[]数组
			values = new Object[params.length / 2];
			for (int i = 1, j = 0; i < params.length; i += 2, j++) {
				values[j] = params[i];
			}
			
			try {
				update = runner.update(conn, sql,values);
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				JdbcUtils.release(conn);
			}
		}
		
		return update;
	}

	@Override
	public int addUser(Object... params) {
		int update = 0;
		String sql = "";
		Object[] values = null;
		Connection conn = JdbcUtils.getConnection();
		if (params != null) {
			// 添加key到sql
			// 后面的runner.update()中会把sql参数给preparedStatement
			sql = "insert into users(";
			for (int i = 0; i < params.length; i += 2) {
				if(i == params.length - 2) {
					sql += (String)params[i] + ") values(";
					break;
				}
				sql += (String)params[i] + ",";	
			}
			
			for (int i = 0; i < params.length / 2; i++) {
				if(i == params.length / 2 - 1) {
					sql += "?)";
					break;
				}
				sql += "?,";
			}
			
			// 为添加values到runner.update()准备一个Object[]数组
			values = new Object[params.length / 2];
			for (int i = 1, j = 0; i < params.length; i += 2, j++) {
				values[j] = params[i];
			}
		}
		try {
			runner.update(conn, sql, values);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return update;
	}

	@Override
	public int updateUser(int id,Object... params) {
		int update = 0;
		String sql_prefix = "update users set ";
		Connection conn = JdbcUtils.getConnection();
		if (params != null) {
			for (int i = 0; i < params.length; i += 2) {
				String sql = sql_prefix + params[i] + " = " + params[i + 1] + " where id = ? ";
				try {
					update = runner.update(conn, sql,id);
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		
		return update;
	}

	@Override
	public int updateUserByCondition(int id, String key, String value) {
		int update = 0;
		Connection conn = JdbcUtils.getConnection();
		String sql_prefix = "update users set ";
		String sql = sql_prefix + key + " = ? where id = ?";
//		System.out.println(sql);
		try {
			update = runner.update(conn, sql, value, id);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return update;
	}
}
